﻿#include "dbutil.h"

DbUtil::DbUtil()
{

}

const QString DbUtil::SQLITE_CONNECTION_NAME = "fy-SQLITE";

void DbUtil::addSqlite()
{
    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE", DbUtil::SQLITE_CONNECTION_NAME);
    database.setDatabaseName(AppSettings::getInstance().m_sqlite_dbDbname);
    database.setUserName(AppSettings::getInstance().m_sqlite_dbUsername);
    database.setPassword(AppSettings::getInstance().m_sqlite_dbPwd);
}

void DbUtil::initSqlLite()
{
    addSqlite();

    QSqlDatabase database = QSqlDatabase::database(DbUtil::SQLITE_CONNECTION_NAME, true);
    if (database.open())
    {
        QSqlQuery sql_query(database);

        QString drop_role_sql = "drop table if exists t_role";
        if(sql_query.exec(drop_role_sql))
        {
            qDebug() << "drop t_role";
        }

        QString create_role_sql = "create table t_role (role_id INTEGER PRIMARY KEY, role_name varchar(20))";
        if(sql_query.exec(create_role_sql))
        {
            qDebug() << "create t_role";
        }

        //QString insert_role_sql = "insert into t_role(role_id, role_name) values(1, '管理员')";
        if(sql_query.exec(QString::fromLocal8Bit("insert into t_role(role_id, role_name) values(1, '超级管理员')")))
        {
            qDebug() << "insert t_role admin";
            sql_query.exec(QString::fromLocal8Bit("insert into t_role(role_id, role_name) values(2, '员工')"));
        }

        QString drop_user_sql = "drop table if exists t_user";
        if(sql_query.exec(drop_user_sql))
        {
            qDebug() << "drop t_user";
        }

        QString create_user_sql = "create table t_user(user_id INTEGER PRIMARY KEY, user_name varchar(20), pwd varchar(20), role_id int)";
        if(sql_query.exec(create_user_sql))
        {
            qDebug() << "create t_user";
        }

        QString insert_user_sql = "insert into t_user(user_id, user_name, pwd, role_id) values (1, 'Admin', '123456', 1)";
        if(sql_query.exec(insert_user_sql))
        {
            qDebug() << "insert t_user admin";
            sql_query.exec("insert into t_user(user_id, user_name, pwd, role_id) values (2, 'fy', '123456', 2)");
        }


        QString drop_menu_sql = "drop table if exists t_menu";
        if(sql_query.exec(drop_menu_sql))
        {
            qDebug() << "drop t_menu";
        }

        QString create_menu_sql = "create table t_menu(menu_id INTEGER PRIMARY KEY, menu_name varchar(20), pmenu_id INTEGER, is_menubar INTEGER, is_toolbar INTEGER)";
        if(sql_query.exec(create_menu_sql))
        {
            qDebug() << "create t_menu";
        }

        if(sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (1, '系统', 0, 1, 0);")))
        {
            qDebug() << QString::fromLocal8Bit("insert t_menu 系统");
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (2, '基本信息', 0, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (3, '帮助', 0, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (4, '系统信息', 1, 1, 1);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (5, '重新登录', 1, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (6, '最大化', 1, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (7, '最小化', 1, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (8, '正常', 1, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (9, '退出', 1, 1, 0);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (10, '角色', 2, 1, 1);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (11, '用户', 2, 1, 1);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (12, '菜单', 2, 1, 1);"));
            sql_query.exec(QString::fromLocal8Bit("insert into t_menu(menu_id, menu_name, pmenu_id, is_menubar, is_toolbar) values (13, '关于', 3, 1, 0);"));
        }
    }
}
